/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import DTO.song.Artist;
import DTO.song.Artists;
import DTO.song.Genre;
import DTO.song.Likers;
import DTO.song.Song;
import DTO.song.Songs;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.GregorianCalendar;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.datatype.DatatypeConfigurationException;
import javax.xml.datatype.DatatypeFactory;
import javax.xml.datatype.XMLGregorianCalendar;

/**
 *
 * @author NoiR
 */
public class SongDAO {

    // ThuanNH
    public Songs getDatabase() {
        Songs songList = null;
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = DBConnection.getConnection();
//            stm = con.prepareStatement(
//                    "SELECT [a].*,[b].AlbumID,[b].AlbumTitle,[b].TrackNumber FROM "
//                    + " (SELECT [Songs].*,[Genres].Name FROM [Songs] "
//                    + " INNER JOIN [Genres] ON [Songs].GenreID=[Genres].GenreID) a "
//                    + " INNER JOIN "
//                    + " (SELECT [Songs].SongID, [Albums].AlbumID, [Albums].AlbumTitle, [Tracks].TrackNumber FROM [Songs] "
//                    + " INNER JOIN [Tracks] ON [Songs].SongID=[Tracks].SongID "
//                    + " JOIN [Albums] ON [Tracks].AlbumID = [Albums].AlbumID) b "
//                    + " ON a.SongID = b.SongID ORDER BY a.SongID");
            stm = con.prepareStatement("SELECT [a].* FROM"
                    + "(SELECT [Songs].*,[Genres].Name FROM [Songs]"
                    + "INNER JOIN [Genres] ON [Songs].GenreID=[Genres].GenreID) a");

            songList = new Songs();
            ResultSet rs = stm.executeQuery();
            while (rs.next()) {
                Song song = new Song();
                Genre genre = new Genre();
                // set simple elements.
                song.setSongID(rs.getBigDecimal("SongID").toBigInteger());
                song.setSongTitle(rs.getString("SongTitle"));
                song.setSongViews(rs.getBigDecimal("SongViews").toBigInteger());
                song.setSongDescription(rs.getString("SongDescription"));
                song.setSongSRC(rs.getString("SongSRC"));
                song.setLyrics(rs.getString("Lyrics"));
                song.setLikeNumber(rs.getBigDecimal("LikeNumber").toBigInteger());
                song.setIsMV(rs.getBoolean("isMV"));
                // genre
                genre.setGenreID(rs.getBigDecimal("GenreID").toBigInteger());
                genre.setGenreName(rs.getString("Name"));
                song.setGenre(genre);
                // artists
                song.setArtists(this.bindDatabaseToArtists(con, song.getSongID().intValue()));
                // likers
                song.setLikers(this.bindDatabaseToLikers(con, song.getSongID().intValue()));
                // date created
                Timestamp d = rs.getTimestamp("SongDateCreated");
                GregorianCalendar gcal = new GregorianCalendar();
                gcal.setTime(d);
                XMLGregorianCalendar xgcal = DatatypeFactory.newInstance().newXMLGregorianCalendar(gcal);
                song.setSongDateCreated(xgcal);
                // find album information
                song = getAlbumInformation(song);

                // add to list
                songList.getSong().add(song);
            }

            return songList;
        } catch (DatatypeConfigurationException ex) {
            System.out.println("Parse Date Time error: " + ex.getMessage());
            return songList;
        } catch (SQLException ex) {
            System.out.println("SQL Exception error: " + ex.getMessage());
            return songList;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    System.out.println("Connection error: " + ex.getMessage());
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    System.out.println("SQL Statement error " + ex.getMessage());
                }
            } // end else if
        }
    }

    // ThuanNH
    public boolean saveAllToDatabase(Songs songs) {
        for (int i = 0; i < songs.getSong().size(); i++) {
            Song song = new Song();
            song = songs.getSong().get(i);
            if (SearchSongByID(
                    song.getSongID().intValue())) {
                update(song);
            } else {
                add(song);
            }
        }

        return true;
    }

    // ThuanNH
    public boolean add(Song song) {
        Connection con = null;
        PreparedStatement stmSong = null;
        PreparedStatement stmTracks = null;
        try {
            con = DBConnection.getConnection();
            stmSong = con.prepareStatement("INSERT INTO [Songs]"
                    + "(SongTitle,SongViews,SongDescription,SongSRC,"
                    + "LikeNumber,Lyrics,isMV,GenreID,SongDateCreated) "
                    + "VALUES (?,?,?,?,?,?,?,?,?)");
            stmSong.setString(1, song.getSongTitle());
            stmSong.setInt(2, song.getSongViews().intValue());
            stmSong.setString(3, song.getSongDescription());
            stmSong.setString(4, song.getSongSRC());
            stmSong.setInt(5, song.getLikeNumber().intValue());
            stmSong.setString(6, song.getLyrics());
            stmSong.setBoolean(7, song.isIsMV());
            stmSong.setInt(8, song.getGenre().getGenreID().intValue());

            XMLGregorianCalendar xcal = DatatypeFactory.newInstance().newXMLGregorianCalendar();
            xcal = song.getSongDateCreated();
            java.util.Date dt = xcal.toGregorianCalendar().getTime();
            Date sqlDt = new Date(dt.getTime());

            stmSong.setDate(9, sqlDt);

            stmSong.executeUpdate();

            // Insert to Tracks table
            stmTracks = con.prepareStatement("INSERT INTO [Tracks] "
                    + " (SongID,AlbumID,TrackNumber) "
                    + " VALUES (?,?,?)");

            stmTracks.setInt(1, song.getSongID().intValue());
            stmTracks.setInt(2, song.getAlbumID().intValue());
            stmTracks.setInt(3, song.getTrackNumber().intValue());

            stmTracks.executeUpdate();
            return true;
        } catch (DatatypeConfigurationException ex) {
            System.out.println("Data Parse error: " + ex.getMessage());
            return false;
        } catch (SQLException ex) {
            System.out.println("SQL Exception error: " + ex.getMessage());
            return false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    System.out.println("Connection error: " + ex.getMessage());
                }
            } else if (stmTracks != null && stmSong != null) {
                try {
                    stmTracks.close();
                    stmSong.close();
                } catch (SQLException ex) {
                    System.out.println("SQL Statement error " + ex.getMessage());
                }
            } // end else if
        }
    }

    // ThuanNH
    public boolean update(Song song) {
        Connection con = null;
        PreparedStatement stmSong = null;
        PreparedStatement stmTrack = null;
        try {
            con = DBConnection.getConnection();
            stmSong = con.prepareStatement("UPDATE [Songs] SET "
                    + "[SongTitle]=?,[SongViews]=?,[SongDescription]=?,"
                    + "[SongSRC]=?,[LikeNumber]=?,[Lyrics]=?,"
                    + "[isMV]=?, [GenreID]=?, [SongDateCreated]=?"
                    + " WHERE [SongID]=?");

            // set value
            stmSong.setString(1, song.getSongTitle());
            stmSong.setInt(2, song.getSongViews().intValue());
            stmSong.setString(3, song.getSongDescription());
            stmSong.setString(4, song.getSongSRC());
            stmSong.setInt(5, song.getLikeNumber().intValue());
            stmSong.setString(6, song.getLyrics());
            stmSong.setBoolean(7, song.isIsMV());
            stmSong.setInt(8, song.getGenre().getGenreID().intValue());

            XMLGregorianCalendar xcal = DatatypeFactory.newInstance().newXMLGregorianCalendar();
            xcal = song.getSongDateCreated();
            java.util.Date dt = xcal.toGregorianCalendar().getTime();
            Date sqlDt = new Date(dt.getTime());

            stmSong.setDate(9, sqlDt);
            stmSong.setInt(10, song.getSongID().intValue());

            stmSong.executeUpdate();

            // update track table
            stmTrack = con.prepareStatement("UPDATE [Tracks] "
                    + "SET [AlbumID]=?,[TrackNumber]=? "
                    + "WHERE [SongID]=?");

            stmTrack.setInt(1, song.getAlbumID().intValue());
            stmTrack.setInt(2, song.getTrackNumber().intValue());
            stmTrack.setInt(3, song.getSongID().intValue());

            stmTrack.executeUpdate();

            return true;
        } catch (DatatypeConfigurationException ex) {
            System.out.println("Data parse error: " + ex.getMessage());
            return false;
        } catch (SQLException e) {
            System.out.println("SQL Exception error: " + e.getMessage());
            return false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    System.out.println("Connection error: " + ex.getMessage());
                }
            }
            if (stmSong != null && stmTrack != null) {
                try {
                    stmSong.close();
                    stmTrack.close();
                } catch (SQLException ex) {
                    System.out.println("SQL Statement error " + ex.getMessage());
                }
            } // end else if
        }
    }

    // ThuanNH
    public boolean delete(int songID) {
        Connection con = null;
        PreparedStatement stm = null;

        try {
            con = DBConnection.getConnection();
            stm = con.prepareStatement("DELETE FROM [Songs] "
                    + "WHERE SongID=?");
            stm.setInt(1, songID);

            stm.executeUpdate();
            return true;
        } catch (SQLException ex) {
            System.out.println("SQL Exception error: " + ex.getMessage());
            return false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    System.out.println("Connection error: " + ex.getMessage());
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    System.out.println("SQL Statement error " + ex.getMessage());
                }
            } // end else if
        }
    }

    // ThuanNH
    public Artists bindDatabaseToArtists(Connection con, int id) {
        Artists list = new Artists();
        PreparedStatement stm = null;
        ResultSet rs = null;
        String query = "SELECT [Artists].ArtistID,[Artists].ArtistName"
                + " FROM [Songs] INNER JOIN [SongDetailsByArtist]"
                + " ON [SongDetailsByArtist].SongID = ? JOIN [Artists] "
                + " ON [SongDetailsByArtist].ArtistID = [Artists].ArtistID"
                + " GROUP BY [Artists].ArtistID,[Artists].ArtistName";
        try {
            con = DBConnection.getConnection();
            stm = con.prepareStatement(query);
            stm.setInt(1, id);
            rs = stm.executeQuery();

            while (rs.next()) {
                Artist a = new Artist();
                a.setArtistID(rs.getBigDecimal("ArtistID").toBigInteger());
                a.setArtistName(rs.getString("ArtistName"));

                list.getArtist().add(a);
            }

            return list;
        } catch (SQLException ex) {
            System.out.println("SQL Exception error: " + ex.getMessage());
            return list;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    System.out.println("Connection error: " + ex.getMessage());
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    System.out.println("SQL Statement error " + ex.getMessage());
                }
            } // end else if
        }
    }

    // ThuanNH
    public Likers bindDatabaseToLikers(Connection con, int id) {
        Likers list = null;
        PreparedStatement stm = null;
        try {
            String query = "SELECT [Users].Username"
                    + " FROM Songs INNER JOIN [SongLiker]"
                    + " ON [SongLiker].SongID = ? JOIN"
                    + " Users ON [SongLiker].Username = [Users].Username"
                    + " GROUP BY [Users].Username";
            con = DBConnection.getConnection();
            stm = con.prepareStatement(query);
            stm.setInt(1, id);

            ResultSet rs = stm.executeQuery();
            list = new Likers();
            while (rs.next()) {
                list.getUsername().add(rs.getString("Username"));
            }
            return list;
        } catch (SQLException ex) {
            System.out.println("SQL Exception error: " + ex.getMessage());
            return list;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    System.out.println("Connection error: " + ex.getMessage());
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    System.out.println("SQL Statement error " + ex.getMessage());
                }
            } // end else if
        }
    }

    // ThuanNH
    public boolean SearchSongByID(int id) {
        Connection con = null;
        PreparedStatement stm = null;
        String sql = "SELECT * FROM [SONGS] "
                + "WHERE [SongID]=?";
        Boolean found = false;

        try {
            con = DBConnection.getConnection();
            stm = con.prepareStatement(sql);
            stm.setInt(1, id);

            ResultSet rs = stm.executeQuery();
            if (rs.next()) {
                found = true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(SongDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            return found;
        }
    }

    // ThuanNH
    public Song getAlbumInformation(Song current) {
        if (current == null) {
            return current;
        }

        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = DBConnection.getConnection();
            stm = con.prepareStatement("SELECT [Songs].SongID, [Albums].AlbumID, [Albums].AlbumTitle, [Tracks].TrackNumber "
                    + " FROM [Songs] "
                    + " INNER JOIN [Tracks] ON [Songs].SongID=[Tracks].SongID "
                    + " JOIN [Albums] ON [Tracks].AlbumID = [Albums].AlbumID"
                    + " WHERE [Songs].SongID = ?");
            stm.setInt(1, current.getSongID().intValue());
            ResultSet rs = stm.executeQuery();

            if(rs==null){
                current.setAlbumID(null);
                current.setAlbumTitle(null);
                current.setTrackNumber(null);
            }

            while (rs.next()) {
                current.setAlbumID(rs.getBigDecimal("AlbumID").toBigInteger());
                current.setAlbumTitle(rs.getString("AlbumTitle"));
                current.setTrackNumber(rs.getBigDecimal("TrackNumber").toBigInteger());
            }
        } catch (SQLException ex) {
            Logger.getLogger(SongDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            return current;
        }
    }
}
